# -*- coding: utf-8 -*-

# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: https://docs.scrapy.org/en/latest/topics/item-pipeline.html
import pandas as pd
from snownlp import SnowNLP
from traceback import format_exc
from itemadapter import ItemAdapter
from sqlalchemy import create_engine
from sqlalchemy.types import Integer, String, DECIMAL

class DataAcqrPipeline:
    datalist = list()

    def open_spider(self, spider):
        self.engine = create_engine(f'mysql+pymysql://{self.user}:{self.password}@{self.host}/{self.db}?charset=utf8')
        self.conn = self.engine.connect()

    def __init__(self, host, user, password, db, table, port=3306):
        self.db = db
        self.table = table
        self.host = host
        self.port = port
        self.user = user
        self.password = password

    @classmethod
    def from_crawler(cls, crawler):
        return cls(
            host=crawler.settings.get('DB_HOST'),
            user=crawler.settings.get('DB_USER'),
            password=crawler.settings.get('DB_PWD'),
            db=crawler.settings.get('DB'),
            table=crawler.settings.get('TABLE')
        )

    def clean_data_by_pd(self, data):
        try:
            df =  pd.DataFrame(data)
            df = df.dropna()
            df = df.drop_duplicates(subset=['product_name', 'comment', 'comment_star'])
            df.to_sql(self.table, self.conn, if_exists='append', index=False, dtype={
                'product_id': String(100),
                'product_name': String(100),
                'product_link': String(100),
                'comment': String(255),
                'comment_tm': String(100),
                'comment_star': String(10),
                'sentiments': DECIMAL(3, 2),
            })
        except Exception as e:
            print(format_exc())

    def process_item(self, item, spider):
        item_dict = ItemAdapter(item).asdict()
        item_dict['sentiments'] = round(SnowNLP(item_dict['comment']).sentiments, 2)
        self.datalist.append(item_dict)
        if len(self.datalist) == 1000:
            self.clean_data_by_pd(self.datalist)
            del self.datalist[:]
        return item

    def close_spider(self, spider):
        if self.datalist:
            self.clean_data_by_pd(self.datalist)
        self.conn.close()
